Apra Data Science Challenge 2025

Load Libraries

packages <- c(
  'ggplot2','tidyverse','plotly','leaflet',
  'shiny','shinyWidgets','shinydashboard',
  'xts','forecast','TTR',
  'DT','lubridate','RColorBrewer','scales','stopwords',
  'tidytext','stringr','wordcloud','wordcloud2','scales','dplyr','rfm',
  'SnowballC','textmineR','topicmodels','textclean','tm'
)
for (package in packages) { 
  if (!require(package, character.only = T, quietly = T)) {
    install.packages(package)
    library(package, character.only = T)
  }
}

Load data

crm <- read_csv("CRM_interacions_table.csv")
gift <- read_csv("gift_transactions_table.csv")
video <- read_csv("video_email_data_table.csv")
constituent <- read_csv("constituent_profiles_table.csv")

Part 1: The Untapped Potential: Understanding Our Donor Landscape

CRM Data Overview

# CRM Interaction Type
g <- crm %>%
        group_by(CRM_INTERACTION_TYPE) %>%
        summarise(Total = n()) %>%
        select(CRM_INTERACTION_TYPE, Total) %>%
        ggplot(aes(x = reorder(CRM_INTERACTION_TYPE,Total) ,y = Total))  +
        geom_bar(stat = "identity",width = 0.5, fill='black')  +
        scale_y_continuous(labels = scales::comma) +
        labs(x ="CRM Interaction Type", y = "Count") + coord_flip() +
        theme(legend.text = element_text(size = 12),
              legend.title = element_text(size = 12),
              axis.title = element_text(size = 14),
              axis.text = element_text(size = 12))
      
ggplotly(g)

CRM Interaction Over Time

crm <- crm %>%
        mutate(Year = lubridate::year(CRM_INTERACTION_DATE),
               Quarter = lubridate::quarter(CRM_INTERACTION_DATE),
               Month = lubridate::month(CRM_INTERACTION_DATE, label = TRUE),
               DOW = lubridate::wday(CRM_INTERACTION_DATE, label=TRUE))

CRM Interaction by Year

crm_year <- crm %>%
group_by(Year, CRM_INTERACTION_TYPE) %>%
        summarise(Total = n()) %>%
        select(Year,CRM_INTERACTION_TYPE, Total)

   g <- ggplot(crm_year, aes(as.factor(Year), Total, group=CRM_INTERACTION_TYPE, colour = CRM_INTERACTION_TYPE)) + 
      geom_line( linewidth=1) + theme_minimal() +
      labs(x = "Year", y = "Total", color="CRM Interaction Type") + 
      scale_y_continuous(labels = comma) +
      theme(legend.text = element_text(size = 10),
            legend.title = element_text(size = 10),
            axis.title = element_text(size = 10),
            axis.text = element_text(size = 10),
            axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)

CRM Interaction by Quarter

crm %>%
group_by(Quarter, CRM_INTERACTION_TYPE) %>%
        summarise(Total = n()) %>%
        select(Quarter,CRM_INTERACTION_TYPE, Total) %>% 
      ggplot(aes(as.factor(Quarter), Total, group=CRM_INTERACTION_TYPE, colour = CRM_INTERACTION_TYPE)) + 
      geom_line( linewidth=1) + theme_minimal() +
      labs(x = "Quarter", y = "Total", color="CRM Interaction Type") + 
      scale_y_continuous(labels = comma) +
      theme(legend.text = element_text(size = 10),
            legend.title = element_text(size = 10),
            axis.title = element_text(size = 10),
            axis.text = element_text(size = 10),
            axis.text.x = element_text(angle = 0, hjust = 1))

CRM Interaction by Month

crm %>%
group_by(Month, CRM_INTERACTION_TYPE) %>%
        summarise(Total = n()) %>%
        select(Month,CRM_INTERACTION_TYPE, Total) %>% 
      ggplot(aes(as.factor(Month), Total, group=CRM_INTERACTION_TYPE, colour = CRM_INTERACTION_TYPE)) + 
      geom_line( linewidth=1) + theme_minimal() +
      labs(x = "Quarter", y = "Total", color="CRM Interaction Type") + 
      scale_y_continuous(labels = comma) +
      theme(legend.text = element_text(size = 10),
            legend.title = element_text(size = 10),
            axis.title = element_text(size = 10),
            axis.text = element_text(size = 10),
            axis.text.x = element_text(angle = 0, hjust = 1))

CRM Interaction by Day of Week

crm %>%
group_by(DOW, CRM_INTERACTION_TYPE) %>%
        summarise(Total = n()) %>%
        select(DOW,CRM_INTERACTION_TYPE, Total) %>% 
      ggplot(aes(as.factor(DOW), Total, group=CRM_INTERACTION_TYPE, colour = CRM_INTERACTION_TYPE)) + 
      geom_line( linewidth=1) + theme_minimal() +
      labs(x = "Day of Week", y = "Total", color="CRM Interaction Type") + 
      scale_y_continuous(labels = comma) +
      theme(legend.text = element_text(size = 10),
            legend.title = element_text(size = 10),
            axis.title = element_text(size = 10),
            axis.text = element_text(size = 10),
            axis.text.x = element_text(angle = 0, hjust = 1))

Gift Overview

Gifts by CRM Interaction Type

left_join(gift,crm,by='CONSTITUENT_ID') %>%
  group_by(CRM_INTERACTION_TYPE) %>%
  summarise(Total = sum(AMOUNT)) %>%
  select(CRM_INTERACTION_TYPE,Total) %>%
  ggplot(aes(x = reorder(CRM_INTERACTION_TYPE,Total) ,y = Total))  +
        geom_bar(stat = "identity",width = 0.5, fill='black')  +
        scale_y_continuous(labels = scales::comma) +
        labs(x ="CRM Interaction Type", y = "Donations") + coord_flip() +
        theme(legend.text = element_text(size = 12),
              legend.title = element_text(size = 12),
              axis.title = element_text(size = 14),
              axis.text = element_text(size = 12))

Gifts overtime

gift <- gift %>%
        mutate(Year = lubridate::year(GIFT_DATE),
               Quarter = lubridate::quarter(GIFT_DATE),
               Month = lubridate::month(GIFT_DATE, label = TRUE),
               DOW = lubridate::wday(GIFT_DATE, label=TRUE))

Gift by Year

g <- gift %>%
group_by(Year) %>%
        summarise(Total = sum(AMOUNT)) %>%
        select(Year, Total) %>% 
        na.omit() %>%
      ggplot(aes(Year, Total)) + 
      geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
      labs(x = "Year", y = "Total") + 
      scale_y_continuous(labels = comma) +
      theme(legend.text = element_text(size = 10),
            legend.title = element_text(size = 10),
            axis.title = element_text(size = 10),
            axis.text = element_text(size = 10),
            axis.text.x = element_text(angle = 90, hjust = 1))
ggplotly(g)

Gift by Quarter

g <- gift %>%
group_by(Quarter) %>%
        summarise(Total = sum(AMOUNT)) %>%
        select(Quarter, Total) %>% 
        na.omit() %>%
      ggplot(aes(Quarter, Total)) + 
      geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
      labs(x = "Quarter", y = "Total") + 
      scale_y_continuous(labels = comma) +
      theme(legend.text = element_text(size = 10),
            legend.title = element_text(size = 10),
            axis.title = element_text(size = 10),
            axis.text = element_text(size = 10),
            axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)

Gift by Month

g <- gift %>%
group_by(Month) %>%
        summarise(Total = sum(AMOUNT)) %>%
        select(Month, Total) %>% 
        na.omit() %>%
      ggplot(aes(Month, Total)) + 
      geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
      labs(x = "Month", y = "Total") + 
      scale_y_continuous(labels = comma) +
      theme(legend.text = element_text(size = 10),
            legend.title = element_text(size = 10),
            axis.title = element_text(size = 10),
            axis.text = element_text(size = 10),
            axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)

Gift by Day of Week

g <- gift %>%
group_by(DOW) %>%
        summarise(Total = sum(AMOUNT)) %>%
        select(DOW, Total) %>% 
        na.omit() %>%
      ggplot(aes(DOW, Total)) + 
      geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
      labs(x = "Day of Week", y = "Total") + 
      scale_y_continuous(labels = comma) +
      theme(legend.text = element_text(size = 10),
            legend.title = element_text(size = 10),
            axis.title = element_text(size = 10),
            axis.text = element_text(size = 10),
            axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)

Video Overview

Video Views over time

video <- video %>%
        mutate(Year = lubridate::year(SENT_DATE),
               Quarter = lubridate::quarter(SENT_DATE),
               Month = lubridate::month(SENT_DATE, label = TRUE),
               DOW = lubridate::wday(SENT_DATE, label=TRUE))

Video views by year

g <- video %>%
group_by(Year) %>%
        summarise(Total = sum(VIDEO_VIEWS)) %>%
        select(Year, Total) %>% 
        na.omit() %>%
      ggplot(aes(as.factor(Year), Total)) + 
      geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
      labs(x = "Year", y = "Total") + 
      scale_y_continuous(labels = comma) +
      theme(legend.text = element_text(size = 10),
            legend.title = element_text(size = 10),
            axis.title = element_text(size = 10),
            axis.text = element_text(size = 10),
            axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)

Video Views by Quarter

g <- video %>%
group_by(Quarter) %>%
        summarise(Total = sum(VIDEO_VIEWS)) %>%
        select(Quarter, Total) %>% 
        na.omit() %>%
      ggplot(aes(Quarter, Total)) + 
      geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
      labs(x = "Quarter", y = "Total") + 
      scale_y_continuous(labels = comma) +
      theme(legend.text = element_text(size = 10),
            legend.title = element_text(size = 10),
            axis.title = element_text(size = 10),
            axis.text = element_text(size = 10),
            axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)

Video Views by Month

g <- video %>%
group_by(Month) %>%
        summarise(Total = sum(VIDEO_VIEWS)) %>%
        select(Month, Total) %>% 
        na.omit() %>%
      ggplot(aes(Month, Total)) + 
      geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
      labs(x = "Month", y = "Total") + 
      scale_y_continuous(labels = comma) +
      theme(legend.text = element_text(size = 10),
            legend.title = element_text(size = 10),
            axis.title = element_text(size = 10),
            axis.text = element_text(size = 10),
            axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)

Video Views by Day of Week

g <- video %>%
group_by(DOW) %>%
        summarise(Total = sum(VIDEO_VIEWS)) %>%
        select(DOW, Total) %>% 
        na.omit() %>%
      ggplot(aes(DOW, Total)) + 
      geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
      labs(x = "Day of the week", y = "Total") + 
      scale_y_continuous(labels = comma) +
      theme(legend.text = element_text(size = 10),
            legend.title = element_text(size = 10),
            axis.title = element_text(size = 10),
            axis.text = element_text(size = 10),
            axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)

Part 2: A Tale of Portfolios/Customer Segmentation

Donor RFM Analysis

rfm_df <- gift %>%
  select(CONSTITUENT_ID,GIFT_DATE,AMOUNT) %>%
  na.omit()

names(rfm_df)[names(rfm_df) == 'CONSTITUENT_ID'] <- 'customer_id'

#rfm model setup
analysis_date <- lubridate::as_date(today(), tz = "UTC")

report <- rfm_table_order(rfm_df, customer_id,GIFT_DATE,AMOUNT, analysis_date)
#segment
segment_titles <- c("First Grade", "Loyal", "Likely to be Loyal",
                    "New Ones", "Could be Promising", "Require Assistance", "Getting Less Frequent",
                    "Almost Out", "Can't Lose Them", "Don’t Show Up at All")
#numerical thresholds
 r_low <- c(4, 2, 3, 4, 3, 2, 2, 1, 1, 1)
 r_high <- c(5, 5, 5, 5, 4, 3, 3, 2, 1, 2)
 f_low <- c(4, 3, 1, 1, 1, 2, 1, 2, 4, 1)
 f_high <- c(5, 5, 3, 1, 1, 3, 2, 5, 5, 2)
 m_low <- c(4, 3, 1, 1, 1, 2, 1, 2, 4, 1)
 m_high  <- c(5, 5, 3, 1, 1, 3, 2, 5, 5, 2)

divisions<-rfm_segment(report, segment_titles, r_low, r_high, f_low, f_high, m_low, m_high)

division_count <- divisions %>% count(segment) %>% arrange(desc(n)) %>% rename(Segment = segment, Count = n)

RFM heatmap

rfm_plot_heatmap(report)

rfm_plot_bar_chart(report)

rfm_plot_histogram(report)

Donor Lifetime Value

# Calculate average donation per customer
avg_donation_per_customer <- gift %>%
  group_by(CONSTITUENT_ID) %>%
  summarize(avg_revenue = mean(AMOUNT))

# Calculate average donor lifespan (simplified, using the number of months)
avg_donor_lifespan <- gift %>%
  group_by(CONSTITUENT_ID) %>% 
  summarize(avg_lifespan = as.numeric(difftime(max(GIFT_DATE), min(GIFT_DATE), units = "days"))) %>%
  na.omit()

# Calculate CLV
clv_df <- inner_join(avg_donation_per_customer,avg_donor_lifespan,by='CONSTITUENT_ID') %>%
group_by(CONSTITUENT_ID) %>%
  mutate(CLV_calc = avg_revenue * avg_lifespan) %>%
  select(CONSTITUENT_ID,CLV_calc)
#clv <- avg_revenue_per_customer$avg_revenue * avg_lifespan$avg_lifespan

#print(clv)

## CLV version 2
# 2. Calculate key metrics for each customer
customer_data <- transactions %>%
  group_by(customer_id) %>%
  summarise(
    frequency = n() - 1, # Number of repeat purchases
    recency = as.numeric(difftime(max(date), min(date), units = "days")),
    total_revenue = sum(revenue),
    monetary = total_revenue / n()
  )

# 3. Calculate components for historical CLV
# Average Purchase Value (APV)
apv <- sum(transactions$revenue) / nrow(transactions)

# Average Purchase Frequency Rate (APFR)
total_purchases <- nrow(transactions)
total_customers <- length(unique(transactions$customer_id))
apfr <- total_purchases / total_customers

# Customer Value (CV)
cv <- apv * apfr

# 4. Calculate a simple historical CLV
# We need to assume a customer lifetime. Let's assume an average customer lifetime of 365 days.
# And a profit margin of 20%
profit_margin <- 0.20
average_customer_lifetime <- 365 # in days

# Average customer lifespan in relation to the observation period
# Here we calculate average lifespan based on recency
average_lifespan <- mean(customer_data$recency)

# Simple CLV
clv_simple <- cv * average_lifespan * profit_margin

Part 3: The Path Forward: Activating Our Strategy